MIS-250 INFORMATION SYSTEMS Assignment 2 – Analysis with Excel Instructions for the provided excel file  Download the archive that contains the initial excel file. The archive is named Assignment2.zip. Extract the workbook from the archive.  Upload and submit your final Excel file. You may have to create a .zip archive containing the file in order to upload it. Information for the excel workbook file and explanation The excel file contains three worksheets. The first worksheet (Orders) contains formulas and the other two only values. The three worksheets can be used to track inventory for a set of parts, by entering the proper formulas in the cells instead of just numerical values.  The values in Sheet(“Orders”) are the number of parts per month of certain types that were delivered to a warehouse for a particular month. The formula computes the number of parts sold the previous month minus how many were on hand at the start of the month and selects the larger of either this difference or 0. That is the amount to order during the current month.  The values in Sheet(“Sales”) are the number of parts of certain types that were sold to customers from the warehouse during a month.  The values in Sheet(“Available”) represent the number of each part available on the first day of the month. Before you begin, make sure you understand the data in the sheets. To help you understand the data, answer the following questions.  How many parts with number A01 were on hand at the end of January 2016?  How many parts with number A01 were ordered and received by the last day of January 2016?  How many parts with number A01 were sold during the month of January 2016? Follow the instructions 1. Add the first two values and subtract the third one to determine how many A01s were in the warehouse on the first day of February 2016. Compare your computed value to the corresponding value shown in the Sheet(“Available”). 2. Sort all the worksheets so that the part numbers (column headings) are in ascending order alphabetically. Make sure that the corresponding numbers in adjacent rows are also rearranged as you sort. 2 3. Replace the values in Sheet(“Available”) with formulas that compute the number on hand. The calculation is a simple sum of two numbers minus another number. 4. Format the three inventory worksheets so that titles and subtitles stand out and so that negative inventory amounts are in red. 5. Copy the three sheets, accepting the default names and making sure the three copies are in order at the end of the workbook. 6. The company is thinking to use a set order quantity inventory policy which means about always ordering the same amount of a particular part each month, rather than varying the amounts ordered from month to month. The amount ordered each month would vary from part to part, but for a particular part would be the same each month. Your job is to test a set order quantity inventory policy against last year’s data by trying different set order amounts for each part. To create a set order amount policy, replace the formulas in Sheet(“Orders (2)”) so that there is a single fixed value in each column. The value used in one column can be different from the value used in the next column, but the values in a single column should all be the same. That is, the same amount of a particular part will be ordered every month, regardless of how many are sold the prior month. An example Sheet(“Orders (20)”) with a set order amount is shown in Figure 1. Figure 1 Orders Worksheet with Set Order Amount Inventory Policy 7. Try to find a set order amount value for each part so that the quantity on hand on March 1, 2017 is as close as possible to 0 but still non-negative and the number of shortages (red values) is minimized. Stop trying different set order amounts when you think you have found the ‘best’ set order amount for each part (according to the stated criterion). To aid in your analysis, create a chart that compares the March 2017 Available values for the new ordering policy compared to the old policy. ***Note that there is not one “CORRECT” answer to this task. 3 8. How do you think the set order amount inventory policy compares to the policy previously used? Is one better than the other? Why? Write four or five sentences that express your ideas and place these in a worksheet named My Thoughts Grading 10%